﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class NguoiDungDAL
    {
        public static bool KiemTraDangNhap(string TenDangNhap, string MatKhau, ref NguoiDungPublic user_login, ref string message)
        {
            // Common.db.Connection.Open();
            message = "";
            if (!Common.db.DatabaseExists()) //if (Common.db.Connection.State == ConnectionState.Closed && Common.db.CommandTimeout == null) // !Common.db.DatabaseExists())
            {
                message = "Kết nối CSDL thất bại!";
                return false;
            }
            string chuoi = "select MaND,HoTen,TenDangNhap,MaCV,MatKhau from NguoiDung where TenDangNhap=" + "'" + TenDangNhap + "'" + " and MatKhau=" + "'" + MatKhau + "'";
            var nguoidung = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@chuoi);
            user_login = (NguoiDungPublic)nguoidung.FirstOrDefault();

            if (user_login != null)
            {
                return true;
            }
            message = "Sai tên đăng nhập hay mật khẩu!";
            return false;
        }
        public static List<NguoiDungPublic> LoadToanBoNguoiDung()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@"select MaND,HoTen,TenDangNhap,MatKhau,MaCV,NgaySinh,TinhTrang,ISNULL(MaNK,0) from NguoiDung");
            return nd.ToList();
        }
        public static List<NguoiDungPublic> LoadNguoiDungHienTai()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@"select MaND,HoTen,TenDangNhap,MatKhau,ChucVu from NguoiDung where TrangThai=1");
            return nd.ToList();
        }
       
        //public static void DeleteNguoiDung(int MaND)
        //{
        //    var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@"delete from NguoiDung where MaND=" + MaND);

        //}
        //public static string LoadHoTen(string TenDangNhap)
        //{
        //    var nd = Common.db.ExecuteStoreQuery<string>(@"select HoTen from NguoiDung where TenDangNhap=" + TenDangNhap);
        //    return nd.FirstOrDefault();
        //}
        public static NguoiDungPublic LoadNguoiDungByMaND(int MaND)
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@"select MaND,HoTen,TenDangNhap,MaCV,MatKhau,NgaySinh,TinhTrang,ISNULL(MaNK,0) as MaNK from NguoiDung where MaND=" + MaND);
            return nd.FirstOrDefault();
        }
        public static void UpdateTinhTrang(int MaND, bool TinhTrang)
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@"update NguoiDung set TinhTrang=" + "'" + TinhTrang + "'" + "where MaND=" + MaND);
            Common.db.SaveChanges();
        }
        public static List<NguoiDungPublic> LoadNguoiCap()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@"select MaND,HoTen from NguoiDung where MaCV=01 or MaCV=02");
            return nd.ToList();
        }
        public static List<NguoiDungPublic> LoadCanBoHoTich()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@"select MaND,HoTen from NguoiDung where MaCV=06");
            return nd.ToList();
        }
        public static List<NguoiDungPublic> LoadChuTich()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(@"select MaND,HoTen from NguoiDung where MaCV=04 or MaCV=05");
            return nd.ToList();
        }
        public static int LoadMaND()
        {
            var nd = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaND),0) from NguoiDung");
            return nd.FirstOrDefault();
        }
        //public static void InsertNguoiDung(NguoiDungPublic _nd)
        //{
        //    NguoiDungQH nd = new NguoiDungQH();
        //    nd.MaND = _nd.MaND;
        //    nd.TenDangNhap = _nd.TenDangNhap;
        //    nd.MatKhau = _nd.MatKhau;
        //    nd.HoTen = _nd.HoTen;
        //    //nd.MaPX = _nd.MaPX;
        //    nd.MaCV = _nd.MaCV;
        //    //nd.SoDienThoai = _nd.SoDienThoai;
        //    Common.db.AddToNguoiDungQHs(nd);
        //    Common.db.SaveChanges();
        //}
    }
}
